<cfcomponent displayname="KronosQueries" output="false">

    <cffunction access="public" name="getNavEmployees" returntype="query">
        <cfargument default="-1" name="Finance_IDNO" required="false" />
        <cfquery name="this_query" datasource="#SESSION.DataSourceNavDB#">
            SELECT 
            e.No_, 
            e.[First Name] AS FirstName, 
            e.[Middle Name] AS MiddleName, 
            e.[Last Name] AS LastName,
            e.[Job Title] AS JobTitle, 
            e.[Address], 
            e.[Address 2] AS Address2, 
            e.City, 
            e.[Post Code] AS PostCode, 
            e.County AS State, 
            e.[Phone No_] AS PhoneNumber, 
            e.[Mobile Phone No_] AS MobileNumber, 
            e.[E-Mail] AS Email, 
            e.[Birth Date] AS DOB, 
            e.[Social Security No_] AS SSN, 
            e.[Home Phone No_] AS HomePhoneNumber,
            ta.[Tax Authority Code] AS TaxAuthorityCode, 
            ta.[Filing Status Code] AS FilingStatusCode, 
            ta.[Extra Withholding] AS ExtraWithholding,
            ta.Allowances
            FROM 
                [Southcentral Foundation$Employee] AS e INNER JOIN
                [Southcentral Foundation$Employee Tax Auth_ Info] AS ta ON e.No_ = ta.[Employee No_]
            <cfif arguments.Finance_IDNO NEQ -1>
                WHERE e.[No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />
            </cfif>
        </cfquery>
        <cfreturn this_query />
    </cffunction>
    
    
    
    <cffunction access="public" name="getNavEmployees401K125" returntype="query">
        <cfargument default="-1" name="Finance_IDNO" required="true" />
        <cfquery name="this_query" datasource="#SESSION.DataSourceNavDB#">
        SELECT  [Payroll Rate Code] AS PayrollRateCode, Amount
        FROM [Southcentral Foundation$Employee Rate] AS er
        WHERE ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
        ([Payroll Rate Code] = 'FACE VALUE') AND ([Effective Date] IN
            (SELECT MAX([Effective Date]) AS Expr1
            FROM [Southcentral Foundation$Employee Rate]
            WHERE ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
            ([Payroll Rate Code] = 'FACE VALUE')))
        GROUP BY [Payroll Rate Code], Amount
        
        UNION
        
        SELECT  [Payroll Rate Code] AS PayrollRateCode, Amount
        FROM [Southcentral Foundation$Employee Rate] AS er
        WHERE ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
        ([Payroll Rate Code] = '401k PERC') AND ([Effective Date] IN
            (SELECT MAX([Effective Date]) AS Expr1
            FROM [Southcentral Foundation$Employee Rate]
            WHERE ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
            ([Payroll Rate Code] = '401k PERC')))
        GROUP BY [Payroll Rate Code], Amount
        
        UNION
        
        SELECT  [Payroll Rate Code] AS PayrollRateCode, Amount
        FROM [Southcentral Foundation$Employee Rate] AS er
        WHERE ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
        ([Payroll Rate Code] = '125 DC AMT') AND ([Effective Date] IN
            (SELECT     MAX([Effective Date]) AS Expr1
            FROM          [Southcentral Foundation$Employee Rate]
            WHERE      ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
            ([Payroll Rate Code] = '125 DC AMT')))
        GROUP BY [Payroll Rate Code], Amount
        UNION
        
        SELECT  [Payroll Rate Code] AS PayrollRateCode, Amount
        FROM [Southcentral Foundation$Employee Rate] AS er
        WHERE ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
        ([Payroll Rate Code] = '125REIMBAM') AND ([Effective Date] IN
            (SELECT     MAX([Effective Date]) AS Expr1
            FROM          [Southcentral Foundation$Employee Rate]
            WHERE      ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
            ([Payroll Rate Code] = '125REIMBAM')))
        GROUP BY [Payroll Rate Code], Amount
        
        UNION
        
        SELECT  [Payroll Rate Code] AS PayrollRateCode, Amount
        FROM [Southcentral Foundation$Employee Rate] AS er
        WHERE ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
        ([Payroll Rate Code] = 'HOURLY') AND ([Effective Date] IN
            (SELECT     MAX([Effective Date]) AS Expr1
            FROM          [Southcentral Foundation$Employee Rate]
            WHERE      ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
            ([Payroll Rate Code] = 'HOURLY')))
        GROUP BY [Payroll Rate Code], Amount
        
        UNION
        
        SELECT  [Payroll Rate Code] AS PayrollRateCode, Amount
        FROM [Southcentral Foundation$Employee Rate] AS er
        WHERE ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
        ([Payroll Rate Code] = 'SALARY') AND ([Effective Date] IN
            (SELECT     MAX([Effective Date]) AS Expr1
            FROM          [Southcentral Foundation$Employee Rate]
            WHERE      ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
            ([Payroll Rate Code] = 'SALARY')))
        GROUP BY [Payroll Rate Code], Amount
        
        UNION
        
        SELECT  [Payroll Rate Code] AS PayrollRateCode, Amount
        FROM [Southcentral Foundation$Employee Rate] AS er
        WHERE ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
        ([Payroll Rate Code] = 'WORK WEEK') AND ([Effective Date] IN
            (SELECT     MAX([Effective Date]) AS Expr1
            FROM          [Southcentral Foundation$Employee Rate]
            WHERE      ([Employee No_] = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Finance_IDNO#" />) AND 
            ([Payroll Rate Code] = 'WORK WEEK')))
        GROUP BY [Payroll Rate Code], Amount
        </cfquery>
        <cfreturn this_query />
    </cffunction>

    
</cfcomponent>